package com.bamboo.toolbox.exporter;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.intellij.openapi.progress.ProgressIndicator;
import lombok.Data;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.IntStream;


/**
 * @author: 陆志庆
 * @date: 2020/8/5 21:29
 * @version:
 */
public class DatasourceTableGenerator {
    /**
     * 缓存连接
     */
    private static Map<String, Connection> connectionMap = new HashMap<>(1);
    /**
     * 执行进度
     */
    private static ProgressIndicator indicator;
    /**
     * 表名
     */
    private static List<String> tableNames;

    /**
     * TODO:表前缀用于批量查询表
     */
    private static String tableNamePrefix;
    /**
     * excel文件名
     */
    private static String outputFileName;

    /**
     * excel文件输出路径
     */
    private static String outputPath =
            "/Users/work/swaper/swaper/doc";
    /**
     * excel模板文件
     */
    private static String templatePath =
            "/Users/work/swaper/swaper/swaper-plugin/src/main/resources/template/table/表结构模板.xlsx";

    private static String SELECT_TABLES_SQL = "SELECT\n" +
            "    table_name\n" +
            "FROM\n" +
            "    user_tab_comments\n" +
            "WHERE\n" +
            "        table_type = 'TABLE'\n" +
            "    AND\n" +
            "        table_name LIKE '${tableNamePrefix}%'";

    private static final String SELECT_TABLE_NAME_CN_SQL = "SELECT\n" +
            "    comments\n" +
            "FROM\n" +
            "    user_tab_comments\n" +
            "WHERE\n" +
            "        table_type = 'TABLE'\n" +
            "    AND\n" +
            "        table_name = '${table}'";

    private static final String SELECT_TABLE_FIELDS_SQL = "SELECT\n" +
            "    a.column_id AS no,\n" +
            "    b.comments AS field_name,\n" +
            "    a.column_name AS field_code,\n" +
            "    a.data_type\n" +
            "     || '('\n" +
            "     || a.data_length\n" +
            "     || ')' AS data_type,\n" +
            "    a.data_default AS defavlt,\n" +
            "    DECODE(\n" +
            "        a.nullable,\n" +
            "        'N',\n" +
            "        'Y',\n" +
            "        ''\n" +
            "    ) AS null_able\n" +
            "FROM\n" +
            "    user_tab_columns a\n" +
            "    LEFT JOIN user_col_comments b ON\n" +
            "        a.table_name = b.table_name\n" +
            "    AND\n" +
            "        a.column_name = b.column_name\n" +
            "WHERE\n" +
            "    a.table_name = '${table}' and b.column_name not in ('OPERATION_CODE',\n" +
            "    'CREATE_TIME',\n" +
            "    'FK_USER_CREATE',\n" +
            "    'USER_NAME_CREATE',\n" +
            "    'UPDATE_TIME',\n" +
            "    'FK_USER_UPDATE',\n" +
            "    'USER_NAME_UPDATE',\n" +
            "    'DELETE_TIME',\n" +
            "    'FK_USER_DELETE',\n" +
            "    'USER_NAME_DELETE',\n" +
            "    'DATA_VERSION',\n" +
            "    'IS_DATA_LOCKED')\n" +
            "ORDER BY 1 ASC";

    private static final String SELECT_INDEX_SQL = "SELECT\n" +
            "    a.uniqueness index_type,\n" +
            "    b.index_name index_name,\n" +
            "    b.column_name field_name\n" +
            "FROM\n" +
            "    user_indexes a,\n" +
            "    user_ind_columns b\n" +
            "WHERE\n" +
            "        a.table_name = b.table_name\n" +
            "    AND\n" +
            "        a.index_name = b.index_name\n" +
            "    AND\n" +
            "        a.table_name = 'T_CREDIT_ABS_FILE'\n" +
            "ORDER BY a.uniqueness DESC";

    private static final String SELECT_SPECIAL_TABLE_SQL = "SELECT\n" +
            "    table_name\n" +
            "FROM\n" +
            "    user_tables\n" +
            "WHERE\n" +
            "    table_name = '${table}'";

    public static void buildDatabaseProperties(String url,
                                               String username,
                                               String password) {
        DBUtils.url = url;
        DBUtils.username = username;
        DBUtils.password = password;
    }

    public static void setTables(List<String> tables) {
        DatasourceTableGenerator.tableNames = tables;
    }

    public static void setTemplate(String templatePath) {
        DatasourceTableGenerator.templatePath = templatePath;
    }

    public static void setOutputPath(String outputPath) {
        DatasourceTableGenerator.outputPath = outputPath;
    }

    public static void setOutputFileName(String outputFileName) {
        DatasourceTableGenerator.outputFileName = outputFileName;
    }

    public static void setIndicator(ProgressIndicator indicator) {
        DatasourceTableGenerator.indicator = indicator;
    }


    public static void gen() {
        List<Table> tables = fetchTables();
        indicator.setFraction(0.60);
        indicator.setText("已完成获取数据库表信息");
        renderExcel(tables);
    }

    private static List<Table> fetchTables() {
        List<Table> tables = new ArrayList<>();
        if (CollectionUtils.isEmpty(tableNames)
                && StringUtils.isNotBlank(tableNamePrefix)) {
            tableNames = DBUtils.select(SELECT_TABLES_SQL.replace("${tableNamePrefix}", tableNamePrefix), String.class);
        }
        if (CollectionUtils.isEmpty(tableNames)) {
            return tables;
        }
        indicator.setFraction(0.20);
        for (String tableName : tableNames) {
            indicator.setText("正在获取" + tableName + "数据");
            String tableExists = DBUtils.selectOne(SELECT_SPECIAL_TABLE_SQL.replace("${table}", tableName), String.class);
            if (StringUtils.isBlank(tableExists)) {
                continue;
            }
            Table table = new Table();
            table.setTableName(tableName);

            String tableNameCn = DBUtils.selectOne(SELECT_TABLE_NAME_CN_SQL.replace("${table}", tableName), String.class);
            table.setTableNameCn(tableNameCn);

            List<TableField> tableFields = DBUtils.select(SELECT_TABLE_FIELDS_SQL.replace("${table}", tableName), TableField.class);
            for (int i = 0; i < tableFields.size(); i++) {
                tableFields.get(i).setNo(String.valueOf(i));
            }
            table.setFields(tableFields);

            String pkFieldName = "";
            for (TableField field : tableFields) {
                if (null == field.getFieldCode()) {
                    continue;
                }
                if (field.getFieldCode().startsWith("PK_")) {
                    pkFieldName = field.getFieldCode();
                    break;
                }
            }
            table.setPkFieldName(pkFieldName);

            List<Index> indices = DBUtils.select(SELECT_INDEX_SQL.replace("${table}", tableName), Index.class);
            // 填充表索引信息
            IntStream.range(0, indices.size()).forEach(j ->
                    indices.get(j).setNo(new DecimalFormat("00").format(j))
            );
            table.setIndices(indices);
            tables.add(table);
        }
        return tables;
    }

    private static void renderExcel(List<Table> tables) {
        if (CollectionUtils.isEmpty(tables)) {
            throw new RuntimeException("tables is empty!!!!!!!!!");
        }
        copyTemplateSheets(tables);
        ExcelWriter excelWriter = EasyExcel
                .write(outputPath + outputFileName + System.currentTimeMillis() + ".xlsx")
                .withTemplate(outputPath + "表结构模板.xlsx")
                .build();
        indicator.setFraction(0.80);
        for (int i = 0; i < tables.size(); i++) {
            Table table = tables.get(i);
            indicator.setText("生成" + table.getTableName() + "Sheet页");
            // 一个表构建一个sheet页面
            WriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(i)
                    .sheetName(table.getTableNameCn())
                    .build();

            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 填充表列信息
            excelWriter.fill(new FillWrapper("table", table.getFields()), fillConfig, writeSheet);

            excelWriter.fill(new FillWrapper("index", table.getIndices()), fillConfig, writeSheet);

            // 填充表属性新信息
            Map<String, Object> map = new HashMap<>();
            map.put("主键", table.getPkFieldName());
            map.put("tableNameCn", table.getTableNameCn());
            map.put("tableName", table.getTableName());
            excelWriter.fill(map, writeSheet);
        }
        excelWriter.finish();
    }

    public static void copyTemplateSheets(List<Table> tables) {
        // 复制模板文件
        try {
            FileUtils.forceDeleteOnExit(new File(outputPath + "表结构模板.xlsx"));
            FileUtils.copyFile(new File(templatePath), new File(outputPath + "表结构模板.xlsx"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook(new FileInputStream(outputPath + "表结构模板.xlsx"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(outputPath + "表结构模板.xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        for (int i = 0; i < tables.size() - 1; i++) {
            Sheet sheet = workbook.cloneSheet(0);
        }
        for (int i = 0; i < tables.size(); i++) {
            Table table = tables.get(i);
            String tableName = StringUtils.isBlank(table.getTableNameCn()) ? table.getTableName() : table.getTableNameCn();
            workbook.setSheetName(i, tableName);
        }
        try {
            outputStream.flush();
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static void clean() {
        connectionMap = new HashMap<>(1);
        indicator = null;
    }


    public static class DBUtils {
        public static String driver;
        public static String url;
        public static String username;
        public static String password;


        public static Connection createConnection() {
            Connection connection = connectionMap.get(url);
            if (null != connection) {
                return connection;
            } else {
                try {
                    Connection connectionCreate = DriverManager.getConnection(url, username, password);
                    connectionMap.put(url, connectionCreate);
                    return connectionCreate;
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }

        public static <T> List<T> executeQuery(Connection connection, String sql, Class<T> clazz) throws SQLException, IllegalAccessException, InstantiationException, NoSuchMethodException, InvocationTargetException {

            List<T> pojos = new ArrayList<>();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet result = preparedStatement.executeQuery();
            assert result != null;
            while (result.next()) {
                if (String.class.equals(clazz)) {
                    pojos.add((T) result.getString(1));
                } else {
                    T t = clazz.newInstance();
                    java.lang.reflect.Field[] declaredFields = clazz.getDeclaredFields();
                    if (ArrayUtils.isEmpty(declaredFields)) {
                        continue;
                    }
                    for (java.lang.reflect.Field declaredField : declaredFields) {
                        String fieldName = declaredField.getName();

                        String fieldValue = "";
                        try {
                            fieldValue = result.getString(humpToLine(fieldName, false));
                        } catch (Exception ignored) {

                        }
                        declaredField.setAccessible(true);
                        declaredField.set(t, fieldValue);
                    }
                    pojos.add(t);
                }
            }
            return pojos;
        }

        public static <T> List<T> select(String sql, Class<T> clazz) {
            List<T> pojos = new ArrayList<>();
            Connection connection = createConnection();
            try {
                pojos = executeQuery(connection, sql, clazz);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
            return pojos;
        }

        public static <T> T selectOne(String sql, Class<T> clazz) {
            List<T> pojos = select(sql, clazz);
            if (CollectionUtils.isEmpty(pojos)) {
                return null;
            }
            if (pojos.size() > 1) {
                throw new RuntimeException("too many return!");
            }
            return pojos.get(0);
        }

        public static String humpToLine(String param, boolean isUpCase) {
            if (param == null || "".equals(param.trim())) {
                return "";
            }
            int len = param.length();
            StringBuilder sb = new StringBuilder(len);
            for (int i = 0; i < len; i++) {
                char c = param.charAt(i);
                if (Character.isUpperCase(c)) {
                    sb.append("_");
                }
                if (isUpCase) {
                    sb.append(Character.toUpperCase(c));
                } else {
                    sb.append(Character.toLowerCase(c));
                }
            }
            return sb.toString();
        }
    }

    @Data
    public static class Table {
        String tableName;
        String tableNameCn;
        List<TableField> fields;
        List<Index> indices;
        String pkFieldName;
    }

    @Data
    public static class TableField {
        private String no;
        private String fieldName;
        private String fieldCode;
        private String dataType;
        private String defavlt;
        private String nullAble;
        private String mark;
    }

    @Data
    public static class Constraint {
        private String no;
        private String fieldName;
    }

    @Data
    public static class Index {
        private String no;
        private String indexType;
        private String indexName;
        private String fieldName;
    }

}



